package com.vogue.supplymall.order.domain.provider;

import com.vogue.ssm.database.provider.BaseSqlProvider;
import com.vogue.ssm.database.util.DealStatus;
import com.vogue.supplymall.common.domain.dto.EditCartInfo;

/**
 * The type Cart provider.
 *
 * @aouthor: zhangliang
 * @date: Create in 2018-01-10
 */
public class CartSqlProvider extends BaseSqlProvider {
    /**
     * Find string.
     *
     * @param editCartInfo the edit cart info
     * @return the string
     */
    public String find(EditCartInfo editCartInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" o.id oid, ")
                .append(" oi.id oiid, ")
                .append(" oip.id oipid, ")
                /*2018/4/8 增加类别id*/
                .append(" rpc2.id categoryid, ")
                .append(" s.id sid, ")
                .append(" s.syscodeid, ")
                .append(" s.organid, ")
                .append(" s.paymode, ")
                .append(" s.`name` sname, ")
                .append(" s.entname, ")
                .append(" s.disabledflag s_invalid, ")
                .append(" p.id pid, ")
                .append(" p.`name` pname, ")
                .append(" p.disabledflag p_invalid, ")
                .append(" IF(pa.disabledflag = 0,IF(rpc.syscodeid is null,if(p.hiddenflag = 1,1,0),0),1) invalid, ")
//                .append(" rpc.syscodeid, ")
                .append(" oip.attrplanid, ")
                .append(" pa.inventory, ")
                .append(" pa.weight, ")
                .append(" pp1.price public_price, ")
                .append(" pav.`name` unit_name, ")
                .append(" p.hiddenflag, ")
                .append(" pp2.price private_price, ")
                .append(" oip.createdate, ")
                .append(" pi.url imgurl, ")
                .append(" oi.storageid, ")
                .append(" oi.`storage`, ")
                // 20180813 add matchid
                .append(" oi.matchid b_matchid, ")
                .append(" oi.itemquantity ");
        sql.append(" FROM ")
                .append(" order_buyer ob ")
                .append(" INNER JOIN `order` o ON (o.id = ob.orderid) ")
                .append(" INNER JOIN order_item oi ON (oi.orderid = o.id) ")
                .append(" INNER JOIN order_item_product oip ON (oip.orderitemid = oi.id) ")
                .append(" INNER JOIN product p ON (oip.productid = p.id) ")
                .append(" INNER JOIN shop s ON (p.shopid = s.id) ")
                /*2018/4/8 增加类别id*/
                .append(" INNER JOIN rel_product_category rpc2 ON ( p.id = rpc2.productid ) ")
                .append(" INNER JOIN product_img pi on (pi.productid = p.id and listflag = 1) ")
                .append(" INNER JOIN product_attrplan pa ON (p.id = pa.productid AND pa.id = oip.attrplanid ) ")
                .append(" INNER JOIN product_attrplan_detail pad ON (pad.attrplanid = pa.id) ")
                .append(" INNER JOIN product_attrvalue pav ON ( ")
                .append(" pav.id = pad.attrvalueid ")
                .append(" AND pav.typeid = 1 ")
                .append(" ) ")
                .append(" LEFT JOIN rel_product_code rpc ON ( ")
                .append(" p.id = rpc.productid ")
                .append(" AND rpc.syscodeid = ob.syscodeid ")
                .append(" ) ")
                .append(" INNER JOIN product_price pp1 ON ( ")
                .append(" p.id = pp1.productid ")
                .append(" AND pa.id = pp1.attrplanid ")
                .append(" AND pp1.allflag = 1 ")
                .append(" ) ")
                .append(" LEFT JOIN product_price pp2 ON ( ")
                .append(" p.id = pp2.productid ")
                .append(" AND pa.id = pp2.attrplanid ")
                .append(" AND pp2.allflag = 0 ")
                .append(" AND pp2.oneid = ob.syscodeid ")
                // 2018/2/26 匹配时间区间允许的私有价格
                .append(" AND pp2.begindate < NOW() ")
                .append(" AND pp2.enddate > NOW() ")
                .append(" ) ");
        sql.append(" WHERE ")
                .append(" o.commitflag = 0 ")
                .append(" AND oi.disabledflag = 0 ")
                .append(" AND oip.disabledflag = 0 ")
                .append(" AND ob.organid = #{organid} ")
                .append(" AND ob.syscodeid = #{syscodeid} ")
                .append(" AND ob.userid = #{loginId} ")
                .append(" AND ob.orderid = oi.orderid ")
                .append(" GROUP BY ")
                .append(" oi.id ")
                .append(" ORDER BY ")
                .append(" oi.modifydate DESC, oip.createdate DESC ");
        return DealStatus.formatShadeSql(sql.toString());
    }


    /**
     * Find count string.
     *
     * @param editCartInfo the edit cart info
     * @return the string
     */
    public String findCount(EditCartInfo editCartInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" count(DISTINCT oi.id) count ");
        sql.append(" FROM ")
                .append(" order_buyer ob ")
                .append(" INNER JOIN `order` o ON (o.id = ob.orderid) ")
                .append(" INNER JOIN order_item oi ON (oi.orderid = o.id) ")
                .append(" INNER JOIN order_item_product oip ON (oip.orderitemid = oi.id) ")
                .append(" INNER JOIN product p ON (oip.productid = p.id) ")
                .append(" INNER JOIN shop s ON (p.shopid = s.id) ")
                /*2018/4/8 增加类别id*/
                .append(" INNER JOIN rel_product_category rpc2 ON ( p.id = rpc2.productid ) ")
                .append(" INNER JOIN product_img pi on (pi.productid = p.id and listflag = 1) ")
                .append(" INNER JOIN product_attrplan pa ON (p.id = pa.productid AND pa.id = oip.attrplanid ) ")
                .append(" INNER JOIN product_attrplan_detail pad ON (pad.attrplanid = pa.id) ")
                .append(" INNER JOIN product_attrvalue pav ON ( ")
                .append(" pav.id = pad.attrvalueid ")
                .append(" AND pav.typeid = 1 ")
                .append(" ) ")
                .append(" LEFT JOIN rel_product_code rpc ON ( ")
                .append(" p.id = rpc.productid ")
                .append(" AND rpc.syscodeid = ob.syscodeid ")
                .append(" ) ")
                .append(" INNER JOIN product_price pp1 ON ( ")
                .append(" p.id = pp1.productid ")
                .append(" AND pa.id = pp1.attrplanid ")
                .append(" AND pp1.allflag = 1 ")
                .append(" ) ")
                .append(" LEFT JOIN product_price pp2 ON ( ")
                .append(" p.id = pp2.productid ")
                .append(" AND pa.id = pp2.attrplanid ")
                .append(" AND pp2.allflag = 0 ")
                .append(" AND pp2.oneid = ob.syscodeid ")
                // 2018/2/26 匹配时间区间允许的私有价格
                .append(" AND pp2.begindate < NOW() ")
                .append(" AND pp2.enddate > NOW() ")
                .append(" ) ");
        sql.append(" WHERE ")
                .append(" o.commitflag = 0 ")
                .append(" AND oi.disabledflag = 0 ")
                .append(" AND oip.disabledflag = 0 ")
                .append(" AND ob.organid = #{organid} ")
                .append(" AND ob.syscodeid = #{syscodeid} ")
                .append(" AND ob.userid = #{loginId} ")
                .append(" AND ob.orderid = oi.orderid ");
        return DealStatus.formatShadeSql(sql.toString());
    }
    public String findItemAndStorageCount(EditCartInfo editCartInfo) {

        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT count(ob.id) count ")
                .append("from order_buyer ob,`order` o,order_item oi,order_item_product oip, product p, product_attrplan pa, ")
                .append("product_attrplan_detail pad, product_attrvalue pav ")
                .append("where o.commitflag = 0 AND oi.disabledflag = 0 AND oip.disabledflag = 0 ")
                .append("AND ob.organid = #{organid} ")
                .append("AND ob.syscodeid = #{syscodeid} ")
                .append("AND ob.userid = #{loginId} ")
                .append( "AND o.id = ob.orderid ")
                .append("AND oi.orderid = o.id ")
                .append("AND oip.orderitemid = oi.id ")
                .append("AND oip.productid = p.id ")
                .append("AND p.id = pa.productid AND pa.id = oip.attrplanid ")
                .append("AND pad.attrplanid = pa.id ")
                .append("AND pav.typeid = 1 AND pav.id = pad.attrvalueid ")
                .append("AND p.id = #{productId}")
                .append( "and pa.id <> #{attrplanid} ")
                .append("and oi.storageid = #{storageId}");

        return DealStatus.formatShadeSql(sql.toString());
    }


}
